<?php
//Config : Les informations personnels de l'instance (log, pass, etc)
require("../include/config.php");

//API Fonctions : les fonctions fournis de base par l'API
require("../API/php/fonctions.php");

//Header établie la connection à la base $connection
require("../API/php/header.php");

//Fonctions : Fonctions personnelles de l'instance
require("../php/fonctions.php");

//Mode debug
$modeDebug = false;

//Public ou privé (clé obligatoire)
$modePublic = true;

//Mode de sortie text,json,xml,csv
//pour xml et csv $object_retour->data["resultat"] doit contenir qu'un est unique array
$modeSortie = "json";

//Liens de test
// phpsql/exemple.php?milis=123450&keyAuthODA=42c643cc44c593c5c2b4c5f6d40489dd&ctrl=ok&test=hello

//Définition des entrants
$arrayInput = array(
    "ctrl" => null,
    "id" => null
);

//Définition des entrants optionel
$arrayInputOpt = array(
    "option" => null
);

//Récupération des entrants
$arrayValeur = recupInput($arrayInput,$arrayInputOpt);

//Object retour minima
// $object_retour->strErreur string
// $object_retour->data  string
// $object_retour->statut  string

//--------------------------------------------------------------------------
$strSql = "SELECT a.`id`, a.`id_projet`, a.`label`, a.`priorite`, a.`valide`, a.`consome`,
    ROUND((a.`valide` - a.`consome`),2) as 'reste', 
    ROUND((a.`velocite`*a.`priorite`/100),2) as 'velociteObj', 
    ROUND((a.`valide` - a.`consome`) / (a.`velocite`*a.`priorite`/100)) as 'nbSemaine',
    curdate() + INTERVAL ROUND((a.`valide` - a.`consome`) / (a.`velocite`*a.`priorite`/100)) WEEK as 'atterissage'
    FROM `".$prefixTable."tab_projets_metrics` a
    WHERE 1=1
    AND a.`id` = ".$arrayValeur["id"]."
";

// On envois la requète
$connection->exec("SET CHARACTER SET utf8");

$select = $connection->query($strSql) or die('Erreur SQL !'.$strSql.'<br>'.print_r($connection->errorInfo(), true));

// On indique que nous utiliserons les résultats en tant qu'objet
$select->setFetchMode(PDO::FETCH_OBJ);

// On transforme les résultats en tableaux d'objet
$metrics = new stdClass();
$metrics->data = $select->fetchAll(PDO::FETCH_OBJ);
$metrics->nombre = count($metrics->data);

$object_retour->data["metrics"] = $metrics;

//--------------------------------------------------------------------------
$strSql = "SELECT MIN(a.`workDate`) as 'minWorkDate',(
    SELECT ROUND((SUM(a.`velocite`)/count(*)),2)
    FROM (
        SELECT b.`week`, SUM(b.`work`) as 'velocite'
        FROM `ria`.`riawork` b
        WHERE 1=1
        AND IF((".$metrics->data[0]->id_projet." = 0),1,IF((".$metrics->data[0]->id_projet." = b.`idProject`), 1,0)) = 1
        AND b.`idProject` in (
            SELECT DISTINCT e.`id`
                FROM `ria`.`riaproject` e
                WHERE 1=1
                AND e.`idStatus` <> 4
        )
        GROUP BY b.`week`
    ) a 
) as 'velociteConst'
FROM `ria`.`riawork` a
WHERE 1=1
AND IF((".$metrics->data[0]->id_projet." = 0),1,IF((".$metrics->data[0]->id_projet." = a.`idProject`), 1,0)) = 1
AND a.`idProject` in (
    SELECT DISTINCT f.`id`
        FROM `ria`.`riaproject` f
        WHERE 1=1
        AND f.`idStatus` <> 4
)
";

// On envois la requète
$select = $connection->query($strSql) or die('Erreur SQL !'.$strSql.'<br>'.print_r($connection->errorInfo(), true));

// On indique que nous utiliserons les résultats en tant qu'objet
$select->setFetchMode(PDO::FETCH_OBJ);

// On transforme les résultats en tableaux d'objet
$detailsRIA = new stdClass();
$detailsRIA->data = $select->fetchAll(PDO::FETCH_OBJ);
$detailsRIA->nombre = count($detailsRIA->data);

$object_retour->data["detailsRIA"] = $detailsRIA;

//--------------------------------------------------------------------------
$strSql = "CREATE TEMPORARY TABLE `tmp_histoConso` (
        `week` INT(11) NOT NULL,
        `conso` DECIMAL(5,2),
        PRIMARY KEY(`week`)
)
SELECT b.`week`, SUM(b.`work`) as 'conso'
FROM `ria`.`riawork` b
WHERE 1=1
AND IF((".$metrics->data[0]->id_projet." = 0),1,IF((".$metrics->data[0]->id_projet." = b.`idProject`), 1,0)) = 1
AND b.`idProject` in (
        SELECT DISTINCT e.`id`
                FROM `ria`.`riaproject` e
                WHERE 1=1
                AND e.`idStatus` <> 4
)
GROUP BY b.`week`
;";
$retour = $connection->exec($strSql) or die('Erreur SQL !'.$strSql.'<br>'.print_r($connection->errorInfo(), true));

$strSql = "CREATE TEMPORARY TABLE `tmp_histoConso_bis` (
        `week` INT(11) NOT NULL,
        `conso` DECIMAL(5,2),
        PRIMARY KEY(`week`)
)
SELECT *
FROM `tmp_histoConso`
WHERE 1=1
;";
$retour = $connection->exec($strSql) or die('Erreur SQL !'.$strSql.'<br>'.print_r($connection->errorInfo(), true));

$strSql = "CREATE TEMPORARY TABLE tmp_listWeek (
        `week` INT(11) NOT NULL,
        PRIMARY KEY(`week`)
)
SELECT DISTINCT a.`week`
FROM `ria`.`riawork` a
WHERE 1=1
AND a.`week` >= (
        SELECT MIN(c.`week`)
        FROM `tmp_histoConso` c
)
AND a.`week` <= (
        SELECT MAX(c.`week`)
        FROM `tmp_histoConso_bis` c
)
;";
$retour = $connection->exec($strSql) or die('Erreur SQL !'.$strSql.'<br>'.print_r($connection->errorInfo(), true));

$strSql = "SELECT a.`week`, IFNULL(b.`conso`, 0) as 'conso'
FROM `tmp_listWeek` a
 LEFT OUTER JOIN `tmp_histoConso` b
 ON a.`week` = b.`week`
WHERE 1=1
;";

// On envois la requète
$select = $connection->query($strSql) or die('Erreur SQL !'.$strSql.'<br>'.print_r($connection->errorInfo(), true));

// On indique que nous utiliserons les résultats en tant qu'objet
$select->setFetchMode(PDO::FETCH_OBJ);

// On transforme les résultats en tableaux d'objet
$burn = new stdClass();
$burn->data = $select->fetchAll(PDO::FETCH_OBJ);
$burn->nombre = count($burn->data);

$object_retour->data["burn"] = $burn;

//--------------------------------------------------------------------------
if($modeDebug){
    $strSorti .= ($strSql);
}

require("../API/php/footer.php");
?>